"""
Case Type   : fullsql
Case Name   : 数据准备
Create At   : 2022/01/25
Owner       : zhao-pengcheng-g
Description :
    1.设置enable_copy_server_files=on
    2.清理历史记录,创建用户
    3.切换用户,创建region表nation表,
    4.切换用户,创建supplier表
    5.切换用户,创建customer表
    6.切换用户,创建part表
    7.切换用户,创建partsupp表
    8.切换用户,创建orders表
    9.切换用户,创建lineitem表
    10.导入数据
    11.设置instr_unique_sql_count比较大的值
Expect      :
    1.设置成功
    2.成功
    3.创建成功
    4.创建成功
    5.创建成功
    6.创建成功
    7.创建成功
    8.创建成功
    9.创建成功
    10.导入成功
    11.设置成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common

class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Setup_Case0001 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_path = os.path.join(r'/home',
                                       self.userNode.ssh_user, 'tables')
        show_cmd = f'show enable_copy_server_files;' \
                   f'show instr_unique_sql_count;'
        self.logger.info(show_cmd)
        show_msg = self.sh_primy.execut_db_sql(show_cmd)
        self.logger.info(show_msg)
        self.default_copy_files = show_msg.splitlines()[2].strip()
        self.default_count = show_msg.splitlines()[-2].strip()
        set_cmd = f'set current_schema to public;'
        msg = self.sh_primy.execut_db_sql(set_cmd)
        self.assertIn(self.constant.SET_SUCCESS_MSG, msg,
                      '执行失败:' + set_cmd)

    def test_fullsql(self):
        text = '---step1:设置enable_copy_server_files=on,expect:设置成功 ---'
        self.logger.info(text)
        guc_result_1 = self.sh_primy.execute_gsguc('reload',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        'enable_copy_server_files=on')
        self.assertTrue(guc_result_1, '执行失败:' + text)
        show_msg = self.sh_primy.execut_db_sql('show enable_copy_server_files;')
        self.logger.info(show_msg)
        self.assertIn('on', show_msg, '执行失败:' + text)

        text = '---step2:清理历史记录,创建用户,expect:成功 ---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
                      drop user if exists qql_fullsql cascade;
                      create user qql_fullsql sysadmin password 'Gauss_234'; '''
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('reset_unique_sql' and 't', sql_msg, '执行失败:' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                      self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)

        text = '---step3:切换用户,创建region表nation表,expect:创建成功 ---'
        self.logger.info(text)
        create_tables = f'''set role qql_fullsql password 'Gauss_234';
                       drop table if exists region;
                       create table region(r_regionkey int not null, 
                       r_name char(25) not null, r_comment varchar(152));
                       drop table if exists nation;
                       create table nation(n_nationkey int not null, 
                       n_name char(25) not null, n_regionkey int not null, 
                       n_comment varchar(152)); '''
        create_msg = self.sh_primy.execut_db_sql(create_tables)
        self.logger.info(create_msg)
        drop_assert = create_msg.count(self.constant.DROP_TABLE_SUCCESS) == 2
        create_assert = \
            create_msg.count(self.constant.CREATE_TABLE_SUCCESS) == 2
        self.assertTrue(self.constant.SET_SUCCESS_MSG and
                        drop_assert and create_assert, '执行失败:' + text)

        text = '---step4:切换用户,创建supplier表,expect:创建成功 ---'
        self.logger.info(text)
        create_supplier = f'''set role qql_fullsql password 'Gauss_234';
            drop table if exists supplier;
            create table supplier
            (s_suppkey     bigint not null, 
            s_name        char(25) not null, 
            s_address     varchar(40) not null, 
            s_nationkey   int not null, 
            s_phone       char(15) not null, 
            s_acctbal     decimal(15,2) not null, 
            s_comment     varchar(101) not null
            )
            partition by range(s_nationkey)
            (
            partition s_nationkey_1 values less than(1),
            partition s_nationkey_2 values less than(2),
            partition s_nationkey_3 values less than(3),
            partition s_nationkey_4 values less than(4),
            partition s_nationkey_5 values less than(5),
            partition s_nationkey_6 values less than(6),    
            partition s_nationkey_7 values less than(7),
            partition s_nationkey_8 values less than(8),
            partition s_nationkey_9 values less than(9),
            partition s_nationkey_10 values less than(10),
            partition s_nationkey_11 values less than(11),
            partition s_nationkey_12 values less than(12),
            partition s_nationkey_13 values less than(13),
            partition s_nationkey_14 values less than(14),
            partition s_nationkey_15 values less than(15),
            partition s_nationkey_16 values less than(16),
            partition s_nationkey_17 values less than(17),
            partition s_nationkey_18 values less than(18),
            partition s_nationkey_19 values less than(19),
            partition s_nationkey_20 values less than(20),
            partition s_nationkey_21 values less than(21),
            partition s_nationkey_22 values less than(22),
            partition s_nationkey_23 values less than(23),
            partition s_nationkey_24 values less than(24),
            partition s_nationkey_25 values less than(25));'''
        msg_supplier = self.sh_primy.execut_db_sql(create_supplier)
        self.logger.info(msg_supplier)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_supplier,
                      '执行失败:' + text)

        text = '---step5:切换用户,创建customer表,expect:创建成功 ---'
        self.logger.info(text)
        create_customer = f'''set role qql_fullsql password 'Gauss_234';
             drop table if exists customer;
            create table customer
            ( c_custkey     bigint not null, 
            c_name        varchar(25) not null, 
            c_address     varchar(40) not null, 
            c_nationkey   int not null, 
            c_phone       char(15) not null, 
            c_acctbal     decimal(15,2)   not null, 
            c_mktsegment  char(10) not null, 
            c_comment     varchar(117) not null
            )
            partition by range(c_nationkey)
            (
            partition c_nationkey_1 values less than(1),
            partition c_nationkey_2 values less than(2),
            partition c_nationkey_3 values less than(3),
            partition c_nationkey_4 values less than(4),
            partition c_nationkey_5 values less than(5),
            partition c_nationkey_6 values less than(6),
            partition c_nationkey_7 values less than(7),
            partition c_nationkey_8 values less than(8),
            partition c_nationkey_9 values less than(9),
            partition c_nationkey_10 values less than(10),
            partition c_nationkey_11 values less than(11),
            partition c_nationkey_12 values less than(12), 
            partition c_nationkey_13 values less than(13),
            partition c_nationkey_14 values less than(14),
            partition c_nationkey_15 values less than(15),
            partition c_nationkey_16 values less than(16),
            partition c_nationkey_17 values less than(17),
            partition c_nationkey_18 values less than(18),
            partition c_nationkey_19 values less than(19),
            partition c_nationkey_20 values less than(20),
            partition c_nationkey_21 values less than(21),
            partition c_nationkey_22 values less than(22),
            partition c_nationkey_23 values less than(23),
            partition c_nationkey_24 values less than(24),
            partition c_nationkey_25 values less than(25));'''
        msg_customer = self.sh_primy.execut_db_sql(create_customer)
        self.logger.info(msg_customer)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_customer,
                      '执行失败:' + text)

        text = '---step6:切换用户,创建part表,expect:创建成功 ---'
        self.logger.info(text)
        create_part = f'''set role qql_fullsql password 'Gauss_234';
            drop table if exists part;
            create table part
            (
            p_partkey     bigint not null, 
            p_name        varchar(55) not null, 
            p_mfgr        char(25) not null, 
            p_brand       char(10) not null, 
            p_type        varchar(25) not null, 
            p_size        bigint not null, 
            p_container   char(10) not null, 
            p_retailprice decimal(15,2) not null, 
            p_comment     varchar(23) not null
            )
            partition by range(p_size)
            (
            partition p_size_1 values less than(11),
            partition p_size_2 values less than(21),
            partition p_size_3 values less than(31),
            partition p_size_4 values less than(41),
            partition p_size_5 values less than(51)
            );'''
        msg_part = self.sh_primy.execut_db_sql(create_part)
        self.logger.info(msg_part)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_part,
                      '执行失败:' + text)

        text = '---step7:切换用户,创建partsupp表,expect:创建成功 ---'
        self.logger.info(text)
        create_partsupp = f'''set role qql_fullsql password 'Gauss_234';
            drop table if exists partsupp;
            create table partsupp
            (
            ps_partkey     bigint not null, 
            ps_suppkey     bigint not null, 
            ps_availqty    bigint not null, 
            ps_supplycost  decimal(15,2)  not null, 
            ps_comment     varchar(199) not null
            )
            partition by range(ps_availqty)
            (
            partition ps_availqty_1 values less than(1000),
            partition ps_availqty_2 values less than(2000),
            partition ps_availqty_3 values less than(3000),
            partition ps_availqty_4 values less than(4000),
            partition ps_availqty_5 values less than(5000),
            partition ps_availqty_6 values less than(6000),
            partition ps_availqty_7 values less than(7000),
            partition ps_availqty_8 values less than(8000),
            partition ps_availqty_9 values less than(9000),
            partition ps_availqty_10 values less than(10000)
            );'''
        msg_partsupp = self.sh_primy.execut_db_sql(create_partsupp)
        self.logger.info(msg_partsupp)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_partsupp,
                      '执行失败:' + text)

        text = '---step8:切换用户,创建orders表,expect:创建成功 ---'
        self.logger.info(text)
        create_orders = f'''set role qql_fullsql password 'Gauss_234';
            drop table if exists orders;
            create table orders
            (
            o_orderkey       bigint not null, 
            o_custkey        bigint not null, 
            o_orderstatus    char(1) not null, 
            o_totalprice     decimal(15,2) not null, 
            o_orderdate      date not null, 
            o_orderpriority  char(15) not null, 
            o_clerk          char(15) not null, 
            o_shippriority   bigint not null, 
            o_comment        varchar(79) not null
            )
            partition by range(o_orderdate)
            (
            partition o_orderdate_1 values less than('1993-01-01 00:00:00'),
            partition o_orderdate_2 values less than('1994-01-01 00:00:00'),
            partition o_orderdate_3 values less than('1995-01-01 00:00:00'),
            partition o_orderdate_4 values less than('1996-01-01 00:00:00'),
            partition o_orderdate_5 values less than('1997-01-01 00:00:00'),
            partition o_orderdate_6 values less than('1998-01-01 00:00:00'),
            partition o_orderdate_7 values less than('1999-01-01 00:00:00')
            );'''
        msg_orders = self.sh_primy.execut_db_sql(create_orders)
        self.logger.info(msg_orders)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_orders,
                      '执行失败:' + text)

        text = '---step9:切换用户,创建lineitem表,expect:创建成功 ---'
        self.logger.info(text)
        create_lineitem = f'''set role qql_fullsql password 'Gauss_234';
            drop table if exists lineitem;
            create table lineitem
            (
            l_orderkey    bigint not null, 
            l_partkey     bigint not null, 
            l_suppkey     bigint not null, 
            l_linenumber  bigint not null, 
            l_quantity    decimal(15,2) not null, 
            l_extendedprice  decimal(15,2) not null, 
            l_discount    decimal(15,2) not null, 
            l_tax         decimal(15,2) not null, 
            l_returnflag  char(1) not null, 
            l_linestatus  char(1) not null, 
            l_shipdate    date not null, 
            l_commitdate  date not null, 
            l_receiptdate date not null, 
            l_shipinstruct char(25) not null, 
            l_shipmode     char(10) not null, 
            l_comment      varchar(44) not null
            )
            partition by range(l_shipdate)
            (
            partition l_shipdate_1 values less than('1993-01-01 00:00:00'),
            partition l_shipdate_2 values less than('1994-01-01 00:00:00'),
            partition l_shipdate_3 values less than('1995-01-01 00:00:00'),
            partition l_shipdate_4 values less than('1996-01-01 00:00:00'),
            partition l_shipdate_5 values less than('1997-01-01 00:00:00'),
            partition l_shipdate_6 values less than('1998-01-01 00:00:00'),
            partition l_shipdate_7 values less than('1999-01-01 00:00:00')
            ); '''
        msg_lineitem = self.sh_primy.execut_db_sql(create_lineitem)
        self.logger.info(msg_lineitem)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_lineitem,
                      '执行失败:' + text)

        text = '---step10:导入数据，expect:导入成功---'
        self.logger.info(text)
        mkdir_cmd = f'rm -rf {self.table_path};' \
                    f'mkdir {self.table_path}'
        self.userNode.sh(mkdir_cmd)
        self.common.scp_file(self.userNode, 'customer.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'lineitem.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'nation.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'orders.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'part.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'partsupp.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'region.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'supplier.tbl',
                             self.table_path)
        customer_path = os.path.join(self.table_path, 'customer.tbl')
        lineitem_path = os.path.join(self.table_path, 'lineitem.tbl')
        nation_path = os.path.join(self.table_path, 'nation.tbl')
        orders_path = os.path.join(self.table_path, 'orders.tbl')
        part_path = os.path.join(self.table_path, 'part.tbl')
        partsupp_path = os.path.join(self.table_path, 'partsupp.tbl')
        region_path = os.path.join(self.table_path, 'region.tbl')
        supplier_path = os.path.join(self.table_path, 'supplier.tbl')
        copy_cmd = f'''set role qql_fullsql password 'Gauss_234';
            copy customer from '{customer_path}' using delimiters '|';
            copy lineitem from '{lineitem_path}' using delimiters '|';
            copy nation from '{nation_path}' using delimiters '|'; 
            copy orders from '{orders_path}' using delimiters '|';
            copy part from '{part_path}' using delimiters '|'; 
            copy partsupp from '{partsupp_path}' using delimiters '|';
            copy region from '{region_path}' using delimiters '|';
            copy supplier from '{supplier_path}' using delimiters '|';
            analyze;'''
        self.logger.info(copy_cmd)
        copy_msg = self.sh_primy.execut_db_sql(copy_cmd)
        self.logger.info(copy_msg)
        copy_assert = copy_msg.count('COPY') == 8
        self.assertTrue(copy_assert and
                        self.constant.ANALYZE_SUCCESS_MSG, '执行失败:' + text)

        text = '---step11:设置instr_unique_sql_count比较大的值,expect:设置成功'
        self.logger.info(text)
        guc_result_2 = self.sh_primy.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'instr_unique_sql_count=100000')
        self.assertTrue(guc_result_2, '执行失败:' + text)

    def tearDown(self):
        self.logger.info(
            '---Opengauss_Function_Fullsql_Setup_Case0001 finish---')


